package com.telecom.cms.service;

import java.sql.ResultSet;
import java.util.List;
import java.util.ArrayList;
import java.util.HashMap;

import com.telecom.cms.dao.CimPageAtomDAO;
import com.telecom.cms.dao.CimPageDAO;
import com.telecom.cms.dao.SysGetIdDAO;
import com.telecom.cms.dao.SysLocLatnDAO;
import com.telecom.cms.dao.Dao;
import com.telecom.cms.dao.DaoImp;
import com.telecom.cms.dto.CimPageAtom;
import com.telecom.cms.dto.CimPage;
import com.telecom.cms.dto.SEG_BIZ_ATOM_DEFDTO;
import com.telecom.cms.dao.SEG_BIZ_ATOM_DEFDAO;
import com.telecom.cms.util.Constant;

public class CopyOfCimPageAtomMgr {
	public void save(CimPageAtom campadv) {
		(new CimPageAtomDAO()).insert(campadv);
	}

	public void delete(CimPageAtom campadv) {
		(new CimPageAtomDAO()).delete(campadv);
	}

	public void deletebypageid(String pageid) {
		(new CimPageAtomDAO()).deletebypageid(pageid);
	}

	public List findall(String pageId) {
		List result = (new CimPageAtomDAO()).findAll(pageId);
		return result;
	}

	public String getinfo(String pageId, String custid, String clustertype,
			String latnid) {
		String latncd = (new SysLocLatnDAO()).findById("latn_id", latnid)
				.getLatn_Cd();
		String clutype = "cust_id";
		if (clustertype.equals("1"))
			clutype = "prd_inst_id";

		int iflag = 0;
		List pageatomlist = (new CimPageAtomDAO()).findAll(pageId);
		CimPage cimpage = (new CimPageDAO()).findById(pageId);
		StringBuffer result = new StringBuffer();
		StringBuffer sql = new StringBuffer();
		StringBuffer tabsql = new StringBuffer();

		CimPageAtom inst = null;
		SEG_BIZ_ATOM_DEFDTO dto = null;
		Dao dao = new DaoImp();
		SEG_BIZ_ATOM_DEFDAO atomdao = new SEG_BIZ_ATOM_DEFDAO();
		String db = (new Constant()).getPara("DATABASE");
		int hasvalueflag = 0;

		HashMap seglist = new HashMap();// user hashmap to reduce the times to
										// get BIZ_ATOM
		List tablist = new ArrayList();

		sql.append("SELECT ");
		result.append("<tr>");

		for (iflag = 0; iflag < pageatomlist.size(); iflag++) {
			inst = (CimPageAtom) pageatomlist.get(iflag);

			System.out.println("the key is :" + inst.getBizAtomId());
			if (seglist.get("" + inst.getBizAtomId()) != null)
				dto = (SEG_BIZ_ATOM_DEFDTO) seglist.get(""
						+ inst.getBizAtomId());
			else {
				dto = (SEG_BIZ_ATOM_DEFDTO) atomdao.findById(inst
						.getBizAtomId());
				seglist.put("" + inst.getBizAtomId(), dto);
			}

			if (cimpage.getShowType().equals("h"))
				result.append("<th>" + dto.getBIZ_ATOM_NAME() + "</th>");

			if (dto.getBIZ_ATOM_FLAG().equals("money"))
				sql.append(dto.getBAS_TABLE_NAME() + "."
						+ dto.getBAS_TABLE_COL_NAME() + "/100");
			else if (dto.getBIZ_ATOM_FLAG().equals("time"))
				sql.append(dto.getBAS_TABLE_NAME() + "."
						+ dto.getBAS_TABLE_COL_NAME() + "/60");
			else if (dto.getSRC_TABLE_COL_NAME() == null
					|| dto.getSRC_TABLE_COL_NAME().trim().equals(""))
				sql.append(dto.getBAS_TABLE_NAME() + "."
						+ dto.getBAS_TABLE_COL_NAME());
			else {
				sql.append(dto.getCDE_SRC_TABLE_NAME() + "."
						+ dto.getSRC_TABLE_COL_NAME());
				tabsql.append(" left join " + dto.getCDE_SRC_TABLE_NAME()
						+ " on " + dto.getBAS_TABLE_NAME().trim() + "."
						+ dto.getBAS_TABLE_COL_NAME().trim() + "="
						+ dto.getCDE_SRC_TABLE_NAME().trim() + "."
						+ dto.getBAS_TABLE_COL_NAME().trim() + " ");
			}

			if (tablist.size() == 0) {
				tablist.add(dto);
				System.out.println(dto.getBAS_TABLE_NAME());
			} else {
				int i = -1;
				for (i = 0; i < tablist.size(); i++)
					if (((SEG_BIZ_ATOM_DEFDTO) tablist.get(i))
							.getBAS_TABLE_NAME().trim().equals(
									dto.getBAS_TABLE_NAME().trim()))
						break;
				if (i == tablist.size()) {
					System.out.println(dto.getBAS_TABLE_NAME());
					tablist.add(dto);
				}
			}

			if (iflag < pageatomlist.size() - 1)
				sql.append(",");
		}
		if (cimpage.getShowType().equals("x"))
			sql.append("," + dto.getBAS_TABLE_NAME() + ".billing_cycle_id ");

		sql.append(" from ");
		for (int i = 0; i < tablist.size(); i++) {

			sql.append(((SEG_BIZ_ATOM_DEFDTO) tablist.get(i))
					.getBAS_TABLE_NAME());
			if (i != tablist.size() - 1)
				sql.append(",");
		}
		sql.append(" " + tabsql + " where ");
		for (int i = 0; i < tablist.size(); i++) {
			sql.append(((SEG_BIZ_ATOM_DEFDTO) tablist.get(i))
					.getBAS_TABLE_NAME()
					+ "." + clutype + "='" + custid + "' ");
			if (i != tablist.size() - 1)
				sql.append(" and ");
		}

		if (dto.getBAS_TABLE_NAME().toUpperCase().indexOf(
				(new Constant()).getPara("BAS_PRD_INST_FILTER")) > -1)
			sql.append(" and " + dto.getBAS_TABLE_NAME() + "."
					+ (new Constant()).getPara("PRD_INST_FILTER"));

		if (dto.getBAS_TABLE_NAME().toUpperCase().indexOf(
				(new Constant()).getPara("BILLING_TAB_FILTER1")) > -1
				|| dto.getBAS_TABLE_NAME().toUpperCase().indexOf(
						(new Constant()).getPara("BILLING_TAB_FILTER2")) > -1) {
			if (cimpage.getShowType().equals("v"))
				sql.append(" QUALIFY ROW_NUMBER() over (PARTITION BY "
						+ dto.getBAS_TABLE_NAME() + "." + clutype
						+ " ORDER BY " + dto.getBAS_TABLE_NAME()
						+ ".billing_cycle_id DESC) = 1");
			// sql.append(" QUALIFY ROW_NUMBER() over (PARTITION BY "+clutype+"
			// ORDER BY "+dto.getBAS_TABLE_NAME()+".billing_cycle_id DESC) =
			// 1");
			else
				sql.append(" ORDER BY " + dto.getBAS_TABLE_NAME()
						+ ".billing_cycle_id DESC ");
		}

		if (cimpage.getShowType().equals("v"))
			result.append("<th colspan=2>" + cimpage.getPageName() + "</th>");
		result.append("</tr>");

		ResultSet rs2;

		try {
			/*
			 * rs2 = dao.select((new Constant()).changeLatnId(latncd,
			 * sql.toString())); if (cimpage.getShowType().equals("v"))
			 * result.append("<tr>");
			 * 
			 * while (rs2.next()) { hasvalueflag=1; // write info in rows if
			 * (cimpage.getShowType().equals("h")) { result.append("<tr>");
			 * for (int i = 1; i <= iflag; i++) { inst = (CimPageAtom)
			 * pageatomlist.get(i - 1);
			 * 
			 * //get biz_atom info from hashmap directly
			 * if(seglist.get(""+inst.getBizAtomId())!=null)
			 * dto=(SEG_BIZ_ATOM_DEFDTO)seglist.get(""+inst.getBizAtomId());
			 * else { dto = (SEG_BIZ_ATOM_DEFDTO)
			 * atomdao.findById(inst.getBizAtomId());
			 * seglist.put(""+inst.getBizAtomId(), dto); }
			 * 
			 * if(dto.getBAS_TABLE_COL_NAME().toUpperCase().indexOf("ACCS_NBR")>-1)
			 * result.append("<td><a
			 * href='prdview.do?method=list&nbrtype=accsnbr&nbr="+rs2.getString(i)+"'>" +
			 * rs2.getString(i) + "</a></td>"); else
			 * if(dto.getBAS_TABLE_COL_NAME().toUpperCase().indexOf("PRD_INST_ID")>-1)
			 * result.append("<td><a
			 * href='prdview.do?method=list&nbrtype=prdinstid&nbr="+rs2.getString(i)+"'>" +
			 * rs2.getString(i) + "</a></td>");
			 * 
			 * result.append("<td>" + rs2.getString(i) + "</td>"); }
			 * result.append("</tr>"); } else if
			 * (cimpage.getShowType().equals("v")) {//two columns one row int
			 * twoflag = 0; for (int i = 1; i <= iflag; i++) { twoflag =
			 * (twoflag + 1) % 2; inst = (CimPageAtom) pageatomlist.get(i - 1); //
			 * get biz_atom info from hashmap directly
			 * if(seglist.get(""+inst.getBizAtomId())!=null)
			 * dto=(SEG_BIZ_ATOM_DEFDTO)seglist.get(""+inst.getBizAtomId());
			 * else { dto = (SEG_BIZ_ATOM_DEFDTO)
			 * atomdao.findById(inst.getBizAtomId());
			 * seglist.put(""+inst.getBizAtomId(), dto); }
			 * //System.out.println(dto.getBAS_TABLE_COL_NAME());
			 * if(dto.getBAS_TABLE_COL_NAME().toUpperCase().indexOf("CUST_ID")>-1)
			 * result.append("<td><img src='images/li2.gif' />" +
			 * dto.getBIZ_ATOM_NAME() + ":<a
			 * href='custview.do?method=list&custid="+rs2.getString(i)+"'>" +
			 * rs2.getString(i) + "</a></td>"); else result.append("<td><img
			 * src='images/li2.gif' />" + dto.getBIZ_ATOM_NAME() + ":" +
			 * rs2.getString(i) + "</td>"); if (0 == twoflag) result.append("</tr><tr>"); }
			 * if(1 == twoflag) result.append("<td></td>"); result.append("</tr>"); }
			 * else if (cimpage.getShowType().equals("x")) {//one columns one
			 * row,and include billing_cycle_id for (int i = 1; i <= iflag; i++) {
			 * inst = (CimPageAtom) pageatomlist.get(i - 1); // get biz_atom
			 * info from hashmap directly
			 * if(seglist.get(""+inst.getBizAtomId())!=null)
			 * dto=(SEG_BIZ_ATOM_DEFDTO)seglist.get(""+inst.getBizAtomId());
			 * else { dto = (SEG_BIZ_ATOM_DEFDTO)
			 * atomdao.findById(inst.getBizAtomId());
			 * seglist.put(""+inst.getBizAtomId(), dto); } result.append("<tr><td>"+rs2.getString(iflag+1)+"</td><td>" +
			 * dto.getBIZ_ATOM_NAME() + "</td><td>" + rs2.getString(i) + "</td></tr>"); } } }
			 */

			if (0 == hasvalueflag)
				result.append((new Constant()).getPara("NO_DATA"));
			if (cimpage.getShowType().equals("v"))
				result.append("</tr>");
		} catch (Exception e) {
			// TODO Auto-generated catch block
			result.append((new Constant()).getPara("NO_DATA"));
			e.printStackTrace();
		} finally {
			dao.closeconn();
		}

		return result.toString();
	}

	public List findPageatom(String pageid) {
		List pageatomlist = (new CimPageAtomDAO()).findAll(pageid);
		return pageatomlist;
	}

	public String findPrdInstId(String latnid, String accsnbr) {
		return (new SysGetIdDAO()).findPrdInstId(latnid, accsnbr);
	}

	public String findcustidbycert(String latnid, String certnbr) {
		return (new SysGetIdDAO()).findcustidbycert(latnid, certnbr);
	}
}
